﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Runtime.InteropServices;
using System.Threading;
using System.IO;
using System.Collections;
using MySqlDBhelper;


namespace 高频打印
{
    public partial class FormInput : Form
    {
        Hashtable ht = new Hashtable(); //存储条码
       
        public FormInput()
        {
            InitializeComponent();
            this.dataGridView1.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect;
        }



        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog file1 = new OpenFileDialog();
                file1.Filter = "(excel文件)|*.xls;*.xlsx";
                DataSet ds = new DataSet();

                if (file1.ShowDialog() == DialogResult.OK)
                {
                    string fileName = file1.FileName;
                    ds = LoadDataFromExcel(fileName, "导出工作表");

                }


                DataTable str = new DataTable();
                DataTable str1 = new DataTable();

                if (ds.Tables.Count != 0)
                {


                    foreach (System.Data.DataColumn dc in ds.Tables[0].Columns)
                    {
                        str = dc.Table;
                    }
                    str1 = str.Copy();  //复制dt表数据结构
                    str1.Clear();  //清楚数据 
                    for (int i = 0; i < str.Rows.Count; i++)
                    {
                        string aaa = str.Rows[i][0].ToString();
                        if (str.Rows[i][1].ToString() != "")
                        {
                            str1.Rows.Add(str.Rows[i].ItemArray);  //添加数据行            
                        }
                    }

                    //遍历行
                    foreach (DataRow dr in str.Rows)
                    {
                        //遍历列
                        for (int i = 0; i < str.Columns.Count; i++)
                        {

                            string a = dr[i].ToString();
                        }
                    }

                    this.dataGridView1.DataSource = str1;
                    ht.Clear();

                    if (this.dataGridView1.Rows.Count > 0)
                    {
                        int ii = 1;
                        foreach (DataGridViewRow row in this.dataGridView1.Rows)
                        {
                            row.Cells[0].Value = false;
                            row.Cells[1].Value = ii;

                            if (!ht.ContainsKey(row.Cells[12].Value))
                            {
                                goods g = new goods(row.Cells[2].Value.ToString(), row.Cells[3].Value.ToString(), row.Cells[4].Value.ToString(), row.Cells[5].Value.ToString(), row.Cells[6].Value.ToString(), row.Cells[7].Value.ToString(),
                                    row.Cells[8].Value.ToString(), row.Cells[9].Value.ToString(), row.Cells[10].Value.ToString(), row.Cells[11].Value.ToString(), row.Cells[12].Value.ToString(), row.Cells[13].Value.ToString(), row.Cells[14].Value.ToString(), row.Cells[15].Value.ToString(), row.Cells[16].Value.ToString(),
                                    row.Cells[17].Value.ToString(), row.Cells[18].Value.ToString(), row.Cells[19].Value.ToString(), row.Cells[20].Value.ToString(), row.Cells[21].Value.ToString(), row.Cells[22].Value.ToString(), row.Cells[23].Value.ToString(), row.Cells[24].Value.ToString(), row.Cells[25].Value.ToString(),
                                    row.Cells[26].Value.ToString(), row.Cells[27].Value.ToString(),"",helper.currloginuserid);
                                ht.Add(row.Cells[12].Value, g);//条码
                            }

                            ii++;
                        }

                    }
                    // label1.Text = "导入数量：" + (this.dataGridView1.Rows.Count - 1) + "";

                }
            }
            catch
            {
            }
            finally
            {
                label1.Text = "导入数量：" + (this.dataGridView1.Rows.Count - 1) + "";
                dataGridView1.ClearSelection();
            }
        }

        public static DataSet LoadDataFromExcel(string filePath, string name)
        {
            try
            {
                string strConn = string.Format(filePath.EndsWith(".xls") ? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;" : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;IMEX = 1'", filePath);//李小剑 20150807
                //string strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";
                // strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";  
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                string sql = "SELECT * FROM [" + name + "$] ";//可是更改Sheet名称，比如sheet2，等等 
                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, name);
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败! 失败原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }
        private void Form1_Load(object sender, EventArgs e)
        {
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                this.progressBar1.Value = 0;
                label1.Text = "";
                dataGridView1.ClearSelection();
               
                StringBuilder json = new StringBuilder("[");
                foreach (string value in ht.Keys)
                {
                    json.Append("{\"barcode\":\"");
                    json.Append(value);
                    json.Append("\"},");
                }
                String injson = json.ToString();
                injson = injson.Substring(0, injson.Length - 1) + "]";

                if (!injson.Equals("]"))
                {
                    this.progressBar1.Value = 50;
                    String str = DbHelperMySQL.Executepro("pro_checkdata", injson.ToString());
                    msg m = helper.getproreturn(str);
                    String retbarcode = m.Data;
                    if (!retbarcode.Equals(","))
                    {
                        if (retbarcode.Substring(0, 1).Equals(","))
                        {
                            retbarcode = retbarcode.Substring(1, retbarcode.Length - 1);
                        }
                        if (retbarcode.Substring(retbarcode.Length - 1, 1).Equals(","))
                        {
                            retbarcode = retbarcode.Substring(0, retbarcode.Length - 1);
                        }
                        string retbarcodenew = retbarcode.Replace(",", "");
                        int len = retbarcode.Length - retbarcodenew.Length;
                        int j = 0;
                        int k = dataGridView1.RowCount / 50;
                        for (int i = 0; i < dataGridView1.RowCount; i++)
                        {
                            dataGridView1.Rows[i].Selected = false;
                            this.dataGridView1.Rows[i].Cells[0].Value = false;
                            Object barcode = this.dataGridView1.Rows[i].Cells[12].Value;
                            if (barcode != null)
                            {
                                if (retbarcode.Contains(barcode.ToString()))
                                {
                                    dataGridView1.Rows[i].Selected = true;
                                    this.dataGridView1.Rows[i].Cells[0].Value = true;
                                    j++;
                                    if ((len + 1) == j)
                                    {
                                        break;
                                    }
                                }
                            }
                            this.progressBar1.Value = (i / k)+50;
                        }
                        this.progressBar1.Value = 100;
                        label1.Text = "检出：" + (len + 1) + "条数据";
                    }
                    else
                    {
                        this.progressBar1.Value = 100;
                        label1.Text = "检出：0条数据";
                    }
                    MessageBox.Show("检查完成！", "提示信息！", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("无数据！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void dataGridView1_CellMouseMove(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                if (e.RowIndex >= 0)
                {
                    //dataGridView1.ClearSelection();
                    dataGridView1.Rows[e.RowIndex].Selected = true;
                    this.dataGridView1.Rows[e.RowIndex].Cells[0].Value = true;
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime d = DateTime.Now;
                string Input_BatchNo = d.ToString("yyyyMMddHHmmss");
                this.progressBar1.Value = 0;
                Hashtable htcheck = new Hashtable();
                List<goods> list = new List<goods>();
                for (int i = 0; i < dataGridView1.RowCount; i++)
                {
                    String value = this.dataGridView1.Rows[i].Cells[0].Value.ToString();
                    if (value == "True")
                    {
                        String barcode = this.dataGridView1.Rows[i].Cells[12].Value.ToString();
                        if (ht.ContainsKey(barcode))
                        {
                            htcheck.Add(barcode, ht[barcode]);
                        }
                    }
                }

                if (htcheck.Count > 0)
                {
                    StringBuilder json = new StringBuilder("[");
                    foreach (string value in htcheck.Keys)
                    {
                        json.Append("{\"barcode\":\"");
                        json.Append(value);
                        json.Append("\"},");
                    }
                    String injson = json.ToString();
                    injson = injson.Substring(0, injson.Length - 1) + "]";

                    if (!injson.Equals("]"))
                    {
                        String str = DbHelperMySQL.Executepro("pro_checkdata", injson.ToString());
                        msg m = helper.getproreturn(str);
                        String retbarcode = m.Data;
                        if (!retbarcode.Equals(","))
                        {
                            string[] barcodearr = retbarcode.Split(',');
                            if (barcodearr.Length > 0)
                            {
                                foreach (String barcode in barcodearr)
                                {
                                    if (ht.ContainsKey(barcode))
                                    {
                                        list.Add((goods)ht[barcode]);
                                    }
                                }
                            }
                        }
                        else
                        {
                            MessageBox.Show("没有可以插入数据库的数据", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }

                }
                else
                {
                    MessageBox.Show("没有选择数据！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

                int l = list.Count / 100;
                if (list.Count > 0)
                {
                    List<goods> jl = new List<goods>();
                    for (int i = 0; i < list.Count; i++)
                    {
                        goods ig = list[i];
                        ig.Input_BatchNo = Input_BatchNo;
                        jl.Add(ig);
                        if (i % 1000 == 0)
                        {
                            string json = helper.getlisttojson(jl);
                            string result = DbHelperMySQL.Executepro("pro_insertgoods", json);
                            jl.Clear();
                        }
                        else if (i == list.Count - 1)
                        {
                            string json = helper.getlisttojson(jl);
                            string result = DbHelperMySQL.Executepro("pro_insertgoods", json);
                            jl.Clear();
                        }
                        int y = i / l;
                        if(y>=100)
                        this.progressBar1.Value = 100;
                        else
                            this.progressBar1.Value = i / l;
                    }

                    MessageBox.Show("数据导入成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        int flag = 0;
        private void button5_Click(object sender, EventArgs e)
        {
            exebutton5();
        }
        public void exebutton5()
        {
            try
            {
                this.progressBar1.Value = 0;
               switch (flag)
               {
                   case 0:
                       int j = dataGridView1.RowCount / 100;
                       for (int i = 1; i < dataGridView1.RowCount - 1; i++)
                       {
                           object value = this.dataGridView1.Rows[i].Cells[0].Value;
                           if (value != null)
                           {
                               if (i == 0 && value.ToString() == "False")
                               {
                                   CurrencyManager cm = (CurrencyManager)BindingContext[dataGridView1.DataSource];
                                   cm.SuspendBinding(); //挂起数据绑定
                                   dataGridView1.Rows[0].Visible = false;
                                   cm.ResumeBinding(); //恢复数据绑定

                                   continue;
                               }
                               if (value.ToString() == "False")
                               {
                                   dataGridView1.Rows[i].Visible = false;
                               }
                               if (i == (dataGridView1.RowCount - 2))
                               {
                                   this.button5.Text = "撤销清除";
                                   flag = 1;
                               }
                           }

                           this.progressBar1.Value = i/j;
                       }

                       break;
                   case 1:
                       int k = dataGridView1.RowCount / 100;
                       for (int i = 0; i < dataGridView1.RowCount; i++)
                       {
                           dataGridView1.Rows[i].Visible = true;
                           this.progressBar1.Value = i / k;
                       }
                       this.button5.Text = "清除未选中";
                       flag = 0;
                       break;
                   default:
                       break;
               }

               
            }
            catch
            {

            }
        }


       
    }
      
    
}
